from flask import Flask, render_template, request, redirect, url_for
from datetime import datetime
import pymysql

app = Flask(__name__)


# 登陆界面
@app.route("/login", methods=['GET', 'POST'])
def user_login():
    if request.method == "POST":
        if request.form.get('username'):
            username = request.form.get('username')
            password = request.form.get('password')
            # 链接MySQL
            conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8',
                                   db='stu_prt')
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

            search = "select * from students where username=%s and password=%s"
            cursor.execute(search, [username, password])
            user = cursor.fetchone()
            cursor.close()
            conn.close()
            if user:
                return redirect(url_for("student_exam", username=username))
            else:
                return "用户不存在或密码错误，请返回重试！"
            # 关闭

        elif request.form.get('adminUsername'):
            username = request.form.get('adminUsername')
            password = request.form.get('adminPassword')
            # 链接MySQL
            conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8',
                                   db='stu_prt')
            cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

            search = "select * from teachers where username=%s and password=%s"
            cursor.execute(search, [username, password])
            admin = cursor.fetchone()
            if admin:
                return redirect('/teacher')
            else:
                return "用户不存在或密码错误，请返回重试！"
            # 关闭
            cursor.close()
            conn.close()
    return render_template("user_login.html")


# 主界面
@app.route("/", methods=['GET', 'POST'])
def index():
    return render_template("index.html")


# 管理员（教师）界面
@app.route("/teacher", methods=['GET', 'POST'])
def show_teacher():
    # 链接MySQL
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    if request.method == 'POST':
        question_id = request.form['question_id']
        edited_type = request.form['edited_type']
        edited_content = request.form['edited_content']
        edited_options = request.form['edited_options']
        edited_answer = request.form['edited_answer']
        edited_difficulty = request.form['edited_difficulty']
        update_quert = "update questions set type=%s,content=%s,options=%s,answer=%s,difficulty=%s where id=%s"
        cursor.execute(update_quert,
                       [edited_type, edited_content, edited_options, edited_answer, edited_difficulty, question_id, ])
        conn.commit()
    search_questions = "select * from questions"
    cursor.execute(search_questions)
    questions = cursor.fetchall()
    # 查询成绩以及得分情况代码
    search_scores = "select * from scores"
    cursor.execute(search_scores)
    scores = cursor.fetchall()
    # 查询所有用户详细信息
    search_users = "select * from students"
    cursor.execute(search_users)
    students = cursor.fetchall()
    # 考试表
    search_exams = "select * from exams"
    cursor.execute(search_exams)
    exams = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('teacher.html', questions=questions, exams=exams, users=students, scores=scores)


# 注册界面
@app.route("/user/register", methods=['GET', 'POST'])
def user_register():
    if request.method == "POST":
        user = request.form.get('username')
        password = request.form.get('password')
        email = request.form.get('email')
        name = request.form.get('name')
        formatted_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        # 链接MySQL
        conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        search = "select * from students where username=%s"
        cursor.execute(search,user)
        result = cursor.fetchone()
        if result:
            return "用户名已存在，请重试！"
        else:
            sql = "insert into students(username,password,email,name,registration_date) values(%s,%s,%s,%s,%s)"
            cursor.execute(sql, [user, password, email, name, formatted_time])
            conn.commit()
            # 关闭
            cursor.close()
            conn.close()
            return redirect("/login")
    else:
        return render_template("user_register.html")


@app.route("/exam/<username>", methods=['GET', 'POST'])
def student_exam(username):
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    exam_name = "exam-" + username
    sql = "select title from exams where title = %s"
    cursor.execute(sql, [exam_name, ])
    if None == cursor.fetchone():
        # 查询随机题目
        select_1 = "SELECT * FROM questions where difficulty<3 and type='选择题' ORDER BY RAND() LIMIT 2"
        select_2 = "SELECT * FROM questions where difficulty>3 and difficulty<7 and type='选择题' ORDER BY RAND() LIMIT 2"
        select_3 = "SELECT * FROM questions where difficulty>6 and type='选择题' ORDER BY RAND() LIMIT 1"

        add_1 = "SELECT * FROM questions WHERE difficulty<3 AND `type`='填空题' ORDER BY RAND() LIMIT 1"
        add_2 = "SELECT * FROM questions WHERE difficulty>3 AND difficulty<7 AND `type`='填空题' ORDER BY RAND() LIMIT 1"
        add_3 = "SELECT * FROM questions WHERE difficulty>6 AND `type`='填空题' ORDER BY RAND() LIMIT 1"

        pro_1 = "SELECT * FROM questions where difficulty<3 and type='编程题' ORDER BY RAND() LIMIT 1"
        pro_2 = "SELECT * FROM questions where difficulty>3 and type='编程题' ORDER BY RAND() LIMIT 1"
        cursor.execute(select_1)
        sel_1 = cursor.fetchall()
        cursor.execute(select_2)
        sel_2 = cursor.fetchall()
        cursor.execute(select_3)
        sel_3 = cursor.fetchall()
        cursor.execute(add_1)
        a_1 = cursor.fetchall()
        cursor.execute(add_2)
        a_2 = cursor.fetchall()
        cursor.execute(add_3)
        a_3 = cursor.fetchall()
        cursor.execute(pro_1)
        p_1 = cursor.fetchall()
        cursor.execute(pro_2)
        p_2 = cursor.fetchall()
        str_id = ""
        set_1 = sel_1[0]
        str_id = str_id + " " + str(set_1['id'])
        set_2 = sel_1[1]
        str_id = str_id + " " + str(set_2['id'])
        set_3 = sel_2[0]
        str_id = str_id + " " + str(set_3['id'])
        set_4 = sel_2[1]
        str_id = str_id + " " + str(set_4['id'])
        set_5 = sel_3[0]
        str_id = str_id + " " + str(set_5['id'])
        ad_1 = a_1[0]
        str_id = str_id + " " + str(ad_1['id'])
        ad_2 = a_2[0]
        str_id = str_id + " " + str(ad_2['id'])
        ad_3 = a_3[0]
        str_id = str_id + " " + str(ad_3['id'])
        pr_1 = p_1[0]
        str_id = str_id + " " + str(pr_1['id'])
        pr_2 = p_2[0]
        str_id = str_id + " " + str(pr_2['id'])
        start_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        st_time_insert = "insert into exams(title,start_time,questions) values(%s,%s,%s)"
        cursor.execute(st_time_insert, [exam_name, start_time, str_id])
        conn.commit()
        cursor.close()
        conn.close()
        return render_template("exam.html", set_1=set_1, set_2=set_2, set_3=set_3, set_4=set_4, set_5=set_5, add_1=ad_1,
                               add_2=ad_2, add_3=ad_3, pro_1=pr_1, pro_2=pr_2, username=username)
    else:
        return "您已完成考试，无需再次考试！"


@app.route("/submit/<username>", methods=['GET', 'POST'])
def submit(username):
    list = []
    # 获取选择题答案
    answer1 = request.form.get('answer1')
    list += [answer1]
    answer2 = request.form.get('answer2')
    list += [answer2]
    answer3 = request.form.get('answer3')
    list += [answer3]
    answer4 = request.form.get('answer4')
    list += [answer4]
    answer5 = request.form.get('answer5')
    list += [answer5]

    # 获取填空题答案
    fillIn1 = request.form.get('fillIn1')
    list += [fillIn1]
    fillIn2 = request.form.get('fillIn2')
    list += [fillIn2]
    fillIn3 = request.form.get('fillIn3')
    list += [fillIn3]

    # 获取编程题答案
    programming1 = request.form.get('programming1')
    list += [programming1]
    programming2 = request.form.get('programming2')
    list += [programming2]

    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 向考试表中存储结束时间
    end_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    exam_name = "exam-" + username
    end_time_insert = "update exams set end_time =%s where title = %s"
    cursor.execute(end_time_insert, [end_time, exam_name])
    conn.commit()

    # 计算得分
    score = 0
    search_questions = "select questions from exams where title = %s"
    cursor.execute(search_questions, [exam_name, ])
    results = cursor.fetchall()
    # 使用 split() 函数将字符串拆分成列表
    numbers_list = results[0]['questions'].split()
    numbers_list = [int(num) for num in numbers_list]
    for i in range(numbers_list.__len__()):
        search_answers = "select answer from questions where id = %s"
        cursor.execute(search_answers, [numbers_list[i], ])
        que_answer = cursor.fetchone()
        if list[i] == que_answer['answer']:
            score += 10

    # 将 考试id 学生名字id 分数写入成绩表中
    search_exam_id = "select id from exams where title = %s"
    cursor.execute(search_exam_id, [exam_name, ])
    exam_id = cursor.fetchone()['id']
    search_stu_id = "select id from students where username = %s"
    cursor.execute(search_stu_id, [username, ])
    stu_id = cursor.fetchone()['id']
    insert_score = "insert into scores(student_id,exam_id,score) values(%s,%s,%s)"
    cursor.execute(insert_score, [stu_id, exam_id, score])
    conn.commit()
    cursor.close()
    conn.close()

    return render_template('end_exam.html', score=score, username=username)


@app.route('/delete_question/<int:question_id>', methods=['DELETE'])
def delete_question(question_id):
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 在数据库中删除对应的题目
    delete_query = "DELETE FROM questions WHERE id = %s"
    cursor.execute(delete_query, (question_id,))
    conn.commit()
    cursor.close()
    conn.close()

    return "成功"


@app.route('/add_question', methods=['POST'])
def add_question():
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    type = request.form.get('type')
    content = request.form.get('content')
    options = request.form.get('options')
    answer = request.form.get('answer')
    difficulty = request.form.get('difficulty')
    add = "insert into questions(type,content,options,answer,difficulty) values(%s,%s,%s,%s,%s)"
    cursor.execute(add, [type, content, options, answer, difficulty, ])
    conn.commit()

    conn.close()
    cursor.close()

    return redirect('/teacher')


@app.route("/query" ,methods=['GET', 'POST'])
def query():
    if request.method == 'POST':
        conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='stu_prt')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        username = request.form['username']
        search_id = "select id from students where username = %s"
        cursor.execute(search_id, [username, ])
        stu_id = cursor.fetchone()
        if stu_id:
            stu_id = stu_id['id']
            search_score = "select score from scores where student_id = %s"
            cursor.execute(search_score, [stu_id, ])
            score = cursor.fetchone()
            conn.close()
            cursor.close()
            if score:
                return render_template('end_exam.html', score=score['score'], username=username)
            else:
                return "您还未进行考试！"
        else:
            return "用户不存在！"

    return render_template('query.html')


if __name__ == '__main__':
    app.run(host='0.0.0.0')
